import pymysql
import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.types import DATE,FLOAT
from re import sub

"""
config for pymysql
"""
config = {
          'host':'127.0.0.1',
          'port':3306,
          'user':'root',
          'password':'123456',
          'database':'chuanqi_statistics',
          'charset':'utf8mb4',
          'cursorclass':pymysql.cursors.Cursor,
          }

"""
connection config & engine for sqlalchemy
"""
connect_info = 'mysql+pymysql://root:123456@127.0.0.1:3306/chuanqi_statistics?charset=utf8'
engine = create_engine(connect_info) #use sqlalchemy to build link-engine


class MySQLOperator:
    def __init__(self):
        self.db = pymysql.connect(**config)
        self.cursor = self.db.cursor()

    def close(self):
        """
        Close DB Connection
        :return:
        """
        self.db.close()

    def get_avg_decay_radio_by_day(self, model_id):
        cursor = self.cursor
        sql = "select date(vcm.start_data_time) as '时间',	avg(vcm.battery_decay_ratio) as'保持率' " \
              "from vehicle_charge_message vcm " \
              "left join vehicle_static_information vsi on vcm.vin = vsi.vin " \
              "where vsi.vehicle_model_id = '%s' " \
              "and vcm.battery_decay_ratio > 0 " \
              "and (vcm.battery_decay_ratio > 0.8 " \
              "and vcm.battery_decay_ratio < 1.05) " \
              "and (vcm.end_soc - vcm.start_soc >0.5) " \
              "and (vcm.end_data_time - vcm.start_data_time) > 5000 " \
              "group by date(vcm.start_data_time) order by date(vcm.start_data_time)"
        cursor.execute(sql % model_id)
        res = cursor.fetchall()
        # self.db.commit()
        return res

    def get_pd_avg_decay_radio_by_day(self, model_id):
        sql = "select date(vcm.start_data_time) as '时间',	" \
              "avg(vcm.battery_decay_ratio) as'保持率' " \
              "from vehicle_charge_message vcm " \
              "left join vehicle_static_information vsi on vcm.vin = vsi.vin " \
              "where vsi.vehicle_model_id = 'param_model_id' " \
              "and vcm.battery_decay_ratio > 0 " \
              "and (vcm.battery_decay_ratio > 0.8 " \
              "and vcm.battery_decay_ratio < 1.05) " \
              "and (vcm.end_soc - vcm.start_soc >0.5) " \
              "and (vcm.end_data_time - vcm.start_data_time) > 5000 " \
              "and vcm.vin = 'LZ91AE3A1J3LSA208'" \
              "group by date(vcm.start_data_time) order by date(vcm.start_data_time)"
        df = pd.read_sql(sub("param_model_id", 'c2e811465bf549598deba09538b3f0f9', sql),
                     con=engine)
        new_cols = ['time', 'conservation_rate']
        df.columns = new_cols
        return df



if __name__ == '__main__':
    op = MySQLOperator()
    # res = op.get_avg_decay_radio_by_day("c2e811465bf549598deba09538b3f0f9")
    # print(res)

    # df.to_sql(name='test1',
    #           con=engine,
    #           if_exists='append',
    #           index=False,
    #           dtype={
    #               '时间': DATE(),
    #               '保持率': FLOAT()
    #           })
    df = op.get_pd_avg_decay_radio_by_day("c2e811465bf549598deba09538b3f0f9")
    print(df)
